use EnterpriseServices
go


DROP TRIGGER [tr_GroupUsers_dCheckOwnership]
GO
CREATE TRIGGER [tr_GroupUsers_dCheckOwnership] ON [Core].[GroupUsers]
FOR DELETE NOT FOR REPLICATION
AS
BEGIN
	
	/*
		We need to avoid that a Group owner will be deleted if there is no other group owner.
		Else, we can proceed
	*/
	
	DECLARE @GroupId uniqueidentifier
	DECLARE @UserId uniqueidentifier
	
	DECLARE deleted_cursor CURSOR FAST_FORWARD FOR
		SELECT GroupId, UserId FROM deleted WHERE deleted.IsGroupOwner = 1
	
	OPEN deleted_cursor
	
	FETCH NEXT FROM deleted_cursor INTO @GroupId, @UserId
	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF( NOT EXISTS(
				SELECT GroupId FROM Core.GroupUsers WHERE
					@GroupId = GroupId AND
					@UserId <> UserId AND
					IsGroupOwner = 1
			)
		)
		BEGIN
			RAISERROR(N'Cannot delete this group user, because there is no other group owner available.', 18, 1)
			ROLLBACK TRANSACTION
			GOTO outsideloop
		END
		FETCH NEXT FROM deleted_cursor INTO @GroupId, @UserId
	END
	
outsideloop:

	CLOSE deleted_cursor
	DEALLOCATE deleted_cursor
	
END
GO